/**
 * @author 蔡明宗
 * @date 2012-11-4 上午11:28:33
 */

package com.gossip.data.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.gossip.model.Tip;
import com.gossip.model.TipDiv;

public class TipData {

	public static List<Tip> queryTips() {
		List<Tip> tipList = new ArrayList<Tip>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			Connection con = java.sql.DriverManager.getConnection(
					"jdbc:mysql://localhost/ad", "root", "123456");
			Statement stmt = con.createStatement();
			ResultSet rst = stmt
					.executeQuery("select * from tip order by create_date desc;");

			while (rst.next()) {
				Tip tip = new Tip();
				tip.setId("div" + rst.getString("id"));
				tip.setContent(rst.getString("content"));
				tip.setDivStyle(rst.getString("div_style"));
				tip.setBackground(rst.getString("background"));
				tip.setCreator(rst.getString("creator"));
				tip.setDivStyle(new TipDiv().getTipStyle(tip));
				tipList.add(tip);
			}

			rst.close();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tipList;
	}

	public static void insertTip(Tip tip) {

		if (tip == null)
			return;
		Calendar c = Calendar.getInstance();
		tip.setCreateDate(c.getTime());
		long time = c.getTimeInMillis();
		c.setTimeInMillis(time + 7 * 24 * 60 * 60 * 1000);
		tip.setEndDate(c.getTime());

		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			Connection con = java.sql.DriverManager.getConnection(
					"jdbc:mysql://localhost/ad", "root", "123456");
			Statement stmt = con.createStatement();
			stmt
					.executeUpdate("insert into tip(content,div_style,background,create_date,end_date,creator) values('"
							+ tip.getContent()
							+ "','"
							+ tip.getDivStyle()
							+ "','"
							+ tip.getBackground()
							+ "','"
							+ dateFormat.format(tip.getCreateDate())
							+ "','"
							+ dateFormat.format(tip.getEndDate())
							+ "','"
							+ tip.getCreator() + "')");
			stmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		Calendar c = Calendar.getInstance();
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		System.out.println(dateFormat.format(c.getTime()));
	}
}
